




GO
CREATE PROCEDURE [dbo].[KW_] 

@lvl int 


AS 


insert into KWlvl select  id_val, kw_strt, NULL from KW where levelid = @lvl  order by id_val asc  -- 1 = @levelid


declare @cdecnt int
declare @cdecnt_max int
set @cdecnt = 1 
set @cdecnt_max = (select MAX(id_val) from lvl_from)
WHILE @cdecnt <= @cdecnt_max
BEGIN 
declare @counter int
declare @code varchar (max)
declare @substring varchar(max)
declare @thiskwid int
declare @position int 
declare @i int
declare @imax int
declare @kw_str varchar(max)
declare @kw_st as varchar(max)			
declare @kw_end as varchar(max)			
declare @len_kw_end int	
declare @startposition int
declare @endposition int
declare @string varchar(max)
declare @code_len int

set @code = (select code from lvl_from where id_val = @cdecnt)
set @counter = 1
WHILE @counter > 0 
BEGIN
set @code_len = (select DATALENGTH(code) from lvl_from where id_val = 1)
set @i = 1
set @imax = (select MAX(id_val) from KWlvl)
WHILE @i <= @imax 
	BEGIN
	set @kw_str = (select kw_strt from KWlvl where id_val = @i)
	set @position = CHARINDEX(@kw_str,@code)
	update KWlvl set pos = @position where id_val = @i
	set @i = @i +1 
	END
set @thiskwid =(select id_val from KWlvl where pos =(select MIN(pos) from KWlvl where pos <> 0 ))
set @kw_st = (select kw_strt from KW where id_val = @thiskwid)  
set @kw_end = (select kw_end from KW where id_val = @thiskwid)
set @len_kw_end = (select len_kw_end from KW where id_val = @thiskwid)
set @startposition = CHARINDEX(@kw_st,@code)
set @endposition = CHARINDEX(@kw_end,@code)+@len_kw_end
set @string = SUBSTRING(@code,@startposition,@endposition-@startposition)



insert into lvl_to select @cdecnt,@kw_st,@kw_end,@string,@string,null
delete from lvl_to where kw_st is null 
set @code = SUBSTRING(@code,@endposition,@code_len)
set @counter = (select COUNT(*) from KWlvl where pos <> 0)
END
set @cdecnt = @cdecnt + 1 
END
select code2, COUNT(*)as dupl into #1 from lvl_to group by code2
update lvl_to set dupl = b.dupl from lvl_to a left outer join #1 b on a.code2 = b.code2 
drop table #1
GO
